﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsMenuDAL.cs
/// 作  者：黄纲
/// 时  间：2006.10.31
/// 说  明：ClsMenuDAL类用于系统菜单相关操作
/// </summary>
public class ClsMenuCDAL
{
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    /// <summary>
    /// 根据当前登陆用户获取已授权的系统菜单类型
    /// </summary>
    /// <param name="UserID">用户登陆ID</param>
    /// <param name="Type">菜单类型</param>
    /// <param name="UpType">上级菜单类型</param>
    /// <returns></returns>
    public DataSet GetSysMenuTypes(string UserLogID, int Type, int UpType)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSysMenuTypes", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserLogID;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;
        dat.SelectCommand.Parameters.Add("@uptype", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@uptype"].Value = UpType;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据当前登陆用户获取已授权的系统菜单项
    /// </summary>
    /// <param name="UserID">用户登陆ID</param>
    /// <param name="MenuType">菜单类型</param>
    /// <returns></returns>
    public DataSet GetSysMenuItems(string UserID, int MenuType)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSysMenuItems", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@m_type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@m_type"].Value = MenuType;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据当前登陆用户获取已授权的系统菜单项
    /// </summary>
    /// <param name="UserID">当前用户登录ID</param>
    /// <returns></returns>
    public DataSet GetSysMenuItemsByCurrUserID(string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSysMenuItemsByCurrUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据用户ID获取已授权的系统菜单项
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <returns></returns>
    public DataSet GetSysMenuItemsByUserID(int UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSysMenuItemsByUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@u_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取菜单项中页面对应的按钮
    /// </summary>
    /// <param name="MenuItemID">菜单项ID</param>
    /// <returns></returns>
    public DataSet GetSysButtonsByMenu(int MenuItemID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSysButtonsByMenu", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@m_item_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@m_item_id"].Value = MenuItemID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

     /// <summary>
    /// 根据用户ID和菜单ID获取以授权的按钮
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <param name="MenuItemID">菜单项ID</param>
    /// <returns></returns>
    public DataSet GetSysButtonsByUMID(int UserID, int MenuID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSysButtonsByUMID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@u_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@m_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@m_id"].Value = MenuID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据当前登陆用户获取已授权的部门
    /// </summary>
    /// <param name="UserID">当前用户登录ID</param>
    /// <returns></returns>
    public DataSet GetDeptsByCurrUserID(string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetDeptsByCurrUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据用户ID获取已授权的部门
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <returns></returns>
    public DataSet GetDeptsByUserID(int UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetDeptsByUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@u_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据当前登陆用户获取已授权的仓库
    /// </summary>
    /// <param name="UserID">当前用户登录ID</param>
    /// <returns></returns>
    public DataSet GetDepotsByCurrUserID(string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetDepotsByCurrUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据用户ID获取已授权的仓库
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <returns></returns>
    public DataSet GetDepotsByUserID(int UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetDepotsByUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@u_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据当前登陆用户获取已授权的商品类别
    /// </summary>
    /// <param name="UserID">当前用户登录ID</param>
    /// <returns></returns>
    public DataSet GetProductTypesByCurrUserID(string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetProductTypesByCurrUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据用户ID获取已授权的商品类别
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <returns></returns>
    public DataSet GetProductTypesByUserID(int UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetProductTypesByUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@u_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据当前登陆用户获取已授权的客户类别
    /// </summary>
    /// <param name="UserID">当前用户登录ID</param>
    /// <returns></returns>
    public DataSet GetOderTypesByCurrUserID(string UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetOderTypesByCurrUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 根据用户ID获取已授权的客户类别
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <returns></returns>
    public DataSet GetOderTypesByUserID(int UserID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetOderTypesByUserID", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@u_id"].Value = UserID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 将菜单项授权给用户
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <param name="MenuItemID">菜单项ID</param>
    /// <returns></returns>
    public Boolean MenuLicensedToRolse(int UserID, string MenuItemIDs)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_MenuLicensedToRolse", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = UserID;
        com.Parameters.Add("@m_ids", SqlDbType.NVarChar, 4000);
        com.Parameters["@m_ids"].Value = MenuItemIDs;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 将菜单项对应页面的按钮授权给用户
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <param name="MenuItemID">当前菜单ID</param>
    /// <param name="ButtonIDs">按钮ID</param>
    /// <returns></returns>
    public Boolean ButtonLicensedToRolse(int UserID, int MenuItemID, string ButtonIDs)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_ButtonLicensedToRolse", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = UserID;
        com.Parameters.Add("@m_id", SqlDbType.Int, 4);
        com.Parameters["@m_id"].Value = MenuItemID;
        com.Parameters.Add("@b_ids", SqlDbType.NVarChar, 4000);
        com.Parameters["@b_ids"].Value = ButtonIDs;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据当前登录用户ID获取当前页已授权的按钮
    /// </summary>
    /// <param name="UserLogID">登录用户ID</param>
    /// <param name="CurrPageID">当前页ID</param>
    /// <returns></returns>
    public DataSet GetCurrUserButtonPopedom(string UserLogID, string CurrPageID)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetCurrUserButtonPopedom", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 60);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserLogID;
        dat.SelectCommand.Parameters.Add("@m_id", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@m_id"].Value = CurrPageID;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 将部门授权给用户
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <param name="DeptIDs">部门ID</param>
    /// <returns></returns>
    public Boolean DeptLicensedToRolse(int UserID, string DeptIDs)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeptLicensedToRolse", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = UserID;
        com.Parameters.Add("@d_ids", SqlDbType.NVarChar, 4000);
        com.Parameters["@d_ids"].Value = DeptIDs;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 将仓库授权给用户
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <param name="DepotIDs">仓库ID</param>
    /// <returns></returns>
    public Boolean DepotLicensedToRolse(int UserID, string DepotIDs)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DepotLicensedToRolse", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = UserID;
        com.Parameters.Add("@d_ids", SqlDbType.NVarChar, 4000);
        com.Parameters["@d_ids"].Value = DepotIDs;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 将商品类别授权给用户
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <param name="ProductTypeIDs">商品类别ID</param>
    /// <returns></returns>
    public Boolean ProductTypeLicensedToRolse(int UserID, string ProductTypeIDs)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_ProductTypeLicensedToRolse", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = UserID;
        com.Parameters.Add("@p_t_ids", SqlDbType.NVarChar, 4000);
        com.Parameters["@p_t_ids"].Value = ProductTypeIDs;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 将客户类别授权给用户
    /// </summary>
    /// <param name="UserID">用户ID</param>
    /// <param name="OderTypeIDs">客户类别ID</param>
    /// <returns></returns>
    public Boolean OderTypeLicensedToRolse(int UserID, string OderTypeIDs)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_OderTypeLicensedToRolse", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@u_id", SqlDbType.Int, 4);
        com.Parameters["@u_id"].Value = UserID;
        com.Parameters.Add("@o_t_ids", SqlDbType.NVarChar, 4000);
        com.Parameters["@o_t_ids"].Value = OderTypeIDs;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    ///  获取系统编码
    /// </summary>
    /// <returns></returns>
    public DataSet GetSystemCode()
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetMenuCode", con);

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取或取消系统编码
    /// </summary>
    /// <param name="Mode">类别: 0: 旧的新增模式 1:取消模式</param>
    /// <param name="Co_ID">公司ID號</param>
    /// <param name="Ext_ID">新增时为"",取消时为新增时所获取的系统编码</param>
    /// <param name="Sc_Type">编码关键字类型</param>
    /// <returns></returns>
    public string GetSystemCode(string Mode, string Co_ID, string Ext_ID, int Sc_Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetSystemCode_ID", con);
        SqlParameter PrmOutPut = new SqlParameter();

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@mode", SqlDbType.NVarChar, 1);
        com.Parameters["@mode"].Value = Mode;
        com.Parameters.Add("@co_id", SqlDbType.NVarChar, 20);
        com.Parameters["@co_id"].Value = Co_ID;
        com.Parameters.Add("@ext_id", SqlDbType.NVarChar, 20);
        com.Parameters["@ext_id"].Value = Ext_ID;
        com.Parameters.Add("@sc_type", SqlDbType.Int, 4);
        com.Parameters["@sc_type"].Value = Sc_Type;
        PrmOutPut = com.Parameters.Add("@sc_no", SqlDbType.NVarChar, 30);
        PrmOutPut.Direction = ParameterDirection.Output;
        PrmOutPut = com.Parameters.Add("@errno", SqlDbType.NVarChar, 20);
        PrmOutPut.Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            if (com.Parameters["@errno"].Value == null || com.Parameters["@errno"].Value.ToString() == "")
            {
                if (com.Parameters["@sc_no"].Value != null)
                {
                    return com.Parameters["@sc_no"].Value.ToString();
                }
                else
                {
                    return "Error"; 
                }
            }
            else
            {
                return "Error";
            }
        }
        catch
        {
            return "Error";
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 根据单号获取要取消的系统编码
    /// </summary>
    /// <param name="SQL">要执行的SQL语句</param>
    /// <returns>返回要取消的系统编码</returns>
    public string GetCancelSysCode(string SQL)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetCancelSysCode", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@strSQL", SqlDbType.NVarChar, 200);
        com.Parameters["@strSQL"].Value = SQL;

        try
        {
            con.Open();
            return com.ExecuteScalar().ToString();
        }
        catch
        {
            return "";
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断系统编码是否存在
    /// </summary>
    /// <param name="Sc_ID"></param>
    /// <returns></returns>
    public int ExistSystemCode(string Sc_ID)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_ExistSystemCode", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@sc_id", SqlDbType.NVarChar, 30);
        com.Parameters["@sc_id"].Value = Sc_ID;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 搜索系统编码
    /// </summary>
    /// <param name="Key"></param>
    /// <returns></returns>
    public DataSet SearchSystemCode(string Key)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_SearchSystemCode", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@key", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@key"].Value = Key;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 修改系统编码
    /// </summary>
    /// <param name="ID"></param>
    /// <param name="Sc_Id"></param>
    /// <param name="Sc_Name"></param>
    /// <param name="Sc_No"></param>
    /// <param name="Sc_Rule"></param>
    /// <param name="Sc_Defrule"></param>
    /// <param name="Sc_Flag"></param>
    /// <param name="Sc_Desc"></param>
    /// <returns></returns>
    public Boolean UpdateSystemCode(int ID, string Name)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateMenuCode", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@id", SqlDbType.Bit);
        com.Parameters["@id"].Value = ID;
        com.Parameters.Add("@name", SqlDbType.NVarChar, 60);
        com.Parameters["@name"].Value = Name;
        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
}
